/*
 * SQL 查询语句管理
 */

export const SQL_QUERIES = {
  // 平台会话管理
  upsertPlatformSession: {
    sql: `
      INSERT INTO request_platform_sessions (
        code,
        request_platform_openid,
        request_platform_session_key,
        created_at
      ) VALUES (?, ?, ?, datetime('now'))
      ON CONFLICT(request_platform_openid) 
      DO UPDATE SET 
        code = excluded.code,
        request_platform_session_key = excluded.request_platform_session_key,
        created_at = excluded.created_at
    `,
    requireAuth: false
  },

  // 验证请求管理
  getVerificationRequest: {
    sql: `
      SELECT 
        code,
        request_platform,
        request_identifier,
        verification_method,
        verification_status,
        verification_time,
        created_at
      FROM verification_requests 
      WHERE request_openid = ? 
      ORDER BY created_at DESC 
      LIMIT 1
    `,
    requireAuth: true
  },

  createVerificationRequest: {
    sql: `
      INSERT INTO verification_requests (
        code,
        request_platform,
        request_identifier,
        verification_method,
        verification_code,
        verification_status,
        request_openid,
        created_by,
        created_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, datetime('now'))
    `,
    requireAuth: true
  },

  updateVerificationStatus: {
    sql: `
      UPDATE verification_requests 
      SET verification_status = ?,
          verification_time = datetime('now'),
          verification_ip = ?,
          verification_by = ?
      WHERE code = ? AND request_openid = ?
        AND verification_status = 'pending'
        AND created_at > datetime('now', '-5 minutes')
    `,
    requireAuth: true
  },

  getPendingVerification: {
    sql: `
      SELECT 
        code,
        request_platform,
        request_identifier,
        verification_method,
        verification_code,
        verification_status,
        created_at
      FROM verification_requests 
      WHERE code = ? 
        AND verification_status = 'pending'
        AND created_at > datetime('now', '-5 minutes')
        AND request_openid = ?
      LIMIT 1
    `,
    requireAuth: true
  },

  // 通用查询 - 检查记录是否存在
  // 注意：调用此查询时必须显式指定 requireAuth
  // 例如：{ ...SQL_QUERIES.checkExists, requireAuth: true }
  checkExists: {
    sql: `
      SELECT EXISTS(
        SELECT 1 FROM ?? 
        WHERE ?? = ?
      ) as exists
    `
    // requireAuth 由调用方根据具体场景指定
  },
};

// 执行数据库查询的通用函数
export function executeQuery(db, queryConfig, params = [], context = {}) {
  try {
    if (!queryConfig?.sql) {
      throw new Error('Invalid query config');
    }

    const stmt = db.prepare(queryConfig.sql);
    const boundStmt = stmt.bind(...params);
    
    // 对于 SELECT 查询
    if (queryConfig.sql.trim().toLowerCase().startsWith('select')) {
      // 如果包含 LIMIT 1 或明确指定要单条结果，使用 first()
      if (queryConfig.sql.toLowerCase().includes('limit 1') || queryConfig.single) {
        return {
          success: true,
          data: boundStmt.first()
        };
      }
      // 否则返回所有结果
      return {
        success: true,
        data: boundStmt.all()
      };
    }
    
    // 对于非 SELECT 查询（INSERT, UPDATE, DELETE 等）
    return {
      success: true,
      data: boundStmt.run()
    };

  } catch (error) {
    return {
      success: false,
      error: error.message
    };
  }
}
